﻿Public Class WFrm_Upload_Excel
    Inherits Wfrm_Master

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            CargarSociedades()
        End If
    End Sub
    Sub CargarSociedades()
        Dim rp As New DotNetResponse.SQLPersistence
        cmb_sociedad.DataSource = Database.SOCIEDAD.GET_00(rp)
        cmb_sociedad.DataTextField = "razon_social_sociedad"
        cmb_sociedad.DataValueField = "id_sociedad"
        cmb_sociedad.DataBind()

        If rp.Errores Then
            MsgError(rp.MensajeError)
        End If
    End Sub

    Protected Sub Btn_Agregar_Pregunta_Click(sender As Object, e As EventArgs) Handles Btn_Agregar_Pregunta.Click
        Dim extension As String = FileUpload1.PostedFile.FileName
        If Split(extension, ".")(Split(extension, ".").Length - 1) <> "xls" And Split(extension, ".")(Split(extension, ".").Length - 1) <> "xlsx" Then
            MsgInformativo("Archivo no tiene formato correcto")
            Exit Sub
        End If

        If FileUpload1.PostedFile.ContentLength = 0 Then
            MsgInformativo("Seleccione un archivo")
            Exit Sub
        End If

        'subir archivo
        Try
            Dim nombre As String = Guid.NewGuid.ToString("N") & "." & Split(extension, ".")(Split(extension, ".").Length - 1)
            'Dim archivo As String = "../upload/" & nombre


            FileUpload1.SaveAs(ConfigurationManager.AppSettings("rutaimagen") & nombre)

            Session("archivo") = ConfigurationManager.AppSettings("rutaimagen") & nombre
            Session("sociedad") = cmb_sociedad.SelectedValue

            'LIMPIAR TABLAS
            Dim execDel0 As New DotNetDal.SQLPersistence(True, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)
            Dim rpDel0 As New DotNetResponse.SQLPersistence
            rpDel0.StoredProcedure = "SP_UP_CLEAR"
            rpDel0.AddParametro(Session("sociedad"))
            execDel0.ExecuteNonQuery(rpDel0)
            execDel0.Commit()


            'grabar en base de datos

           
            Dim execDel As New DotNetDal.SQLPersistence(True, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)
            Dim rpDel As New DotNetResponse.SQLPersistence
            rpDel.StoredProcedure = "SP_SAVE_UP_ARCHIVO"
            rpDel.AddParametro(Session("archivo"))
            rpDel.AddParametro(Session("sociedad"))
            execDel.ExecuteNonQuery(rpDel)
            execDel.Commit()

            Response.Redirect("WFRM_MAPEO.ASPX")
        Catch ex As Exception
            MsgError(ex.Message)
            Exit Sub
        End Try

        'Dim cadenaCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("/FORMS_DL/upload/" & nombre) & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        'Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection(cadenaCon)
        'con.Open()
        'Dim adaptador1 As New OleDb.OleDbDataAdapter("SELECT * FROM [PERSONAL$]", con)
        'Dim datos1 As New DataSet
        'adaptador1.Fill(datos1, "XLData")
        'con.Close()

        'Dim errores As Integer
        'Dim i As Integer

        ''Dim execDel As New DotNetDal.SQLPersistence(True, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)
        ''Dim rpDel As New DotNetResponse.SQLPersistence
        ''rpDel.StoredProcedure = "SP_DEL_TBL_CARGA_CTACTE_PUNTOS_00"
        ''rpDel.AddParametro(Session("usuario"))
        ''execDel.ExecuteNonQuery(rpDel)
        ''execDel.Commit()


        'If Not datos1.Tables Is Nothing Then

        '    For i = 0 To datos1.Tables(0).Rows.Count - 1
        '        'Dim exec As New DotNetDal.SQLPersistence(True, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)
        '        'Dim rp As New DotNetResponse.SQLPersistence
        '        '' rp.StoredProcedure = "SP_INST_TBL_CTACTE_PUNTOS_00"
        '        'rp.StoredProcedure = "SP_INST_TBL_CARGA_CTACTE_PUNTOS_00"
        '        'rp.AddParametro(Session("usuario"))
        '        'rp.AddParametro(datos1.Tables(0).Rows(i).Item(0)) 'RUT Usuario
        '        'rp.AddParametro(datos1.Tables(0).Rows(i).Item(1)) 'PROGRAMA
        '        'rp.AddParametro(datos1.Tables(0).Rows(i).Item(2)) 'Puntos asignados
        '        'exec.ExecuteNonQuery(rp)
        '        'exec.Commit()
        '        'If rp.Errores Then
        '        '    errores = errores + 1
        '        'End If

        '    Next
        'End If
        ''If errores > 0 Then
        ''    Response.Write("<script>alert(' NO se han cargado " & errores & " registros, de un total de: " & i & ", favor revise la data y cargue nuevamente')</script>")
        ''    Exit Sub
        ''End If


        ''Dim execProc As New DotNetDal.SQLPersistence(True, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)
        ''Dim rpProc As New DotNetResponse.SQLPersistence
        ''rpProc.StoredProcedure = "SP_GET_TBL_CARGA_CTACTE_PUNTOS_00"
        ''rpProc.AddParametro(Session("usuario"))
        ''execProc.ExecuteDataTable(rpProc)
        ''execProc.Commit()
        ''If rpProc.Errores Then
        ''    Response.Write("<script>alert('" & rpProc.MensajeError & "')</script>")
        ''    Exit Sub
        ''Else

        ''    If Not rpProc.TieneDatos Then
        ''        Response.Write("<script>alert('Proceso cargado exitosamente')</script>")
        ''    End If
        ''End If

     

    End Sub
End Class